package cai.dao.impl;

import java.util.List;

import search.dao.Impl.UniversalDaoImpl;
import cai.dao.ISDDao;

public class SDDao extends UniversalDaoImpl implements ISDDao {

	int success = 20;

	public List getR1(String dateid) {
		String sql = "select r1,count(r1) r1_sum from sd where dateid='"
				+ dateid + "' and success>=" + success
				+ " group by r1 order by r1";
		return queryForList(sql);
	}

	public List getR2(String dateid) {
		String sql = "select r2,count(r2) r2_sum from sd where dateid='"
				+ dateid + "' and success>=" + success
				+ " group by r2 order by r2";
		return queryForList(sql);
	}

	public List getR3(String dateid) {
		String sql = "select r3,count(r3) r3_sum from sd where dateid='"
				+ dateid + "' and success>=" + success
				+ " group by r3 order by r3";
		return queryForList(sql);
	}

	public List getRAvg(String dateid) {
		String sql = "select r,avg(r_sum) r_avg from("
				+ "select r1 r,count(r1) r_sum from sd where dateid='"
				+ dateid
				+ "' and success>="
				+ success
				+ " group by r1 union select r2 r,count(r2) r_sum from sd where dateid='"
				+ dateid
				+ "' and success>="
				+ success
				+ " group by r2 union select r3 r,count(r3) r_sum from sd where dateid='"
				+ dateid + "' and success>=" + success
				+ " group by r3) t group by r order by r ";
		return queryForList(sql);
	}

	
	public List getRSum(String dateid) {
		String sql = "select r,sum(r_sum) r_sum from("
				+ "select r1 r,count(r1) r_sum from sd where dateid='"
				+ dateid
				+ "' and success>="
				+ success
				+ " group by r1 union select r2 r,count(r2) r_sum from sd where dateid='"
				+ dateid
				+ "' and success>="
				+ success
				+ " group by r2 union select r3 r,count(r3) r_sum from sd where dateid='"
				+ dateid + "' and success>=" + success
				+ " group by r3) t group by r order by r ";
		return queryForList(sql);
	}

	
	public List getRSort(String dateid) {
		String sql="select cast(r1 as char(1))+cast(r2 as char(1))+cast(r3 as char(1)) r,count(*) r_sum  from sd where dateid='"+dateid+"' and success>="+success+" group by r1,r2,r3 order by r_sum";
		return queryForList(sql.toString());
	}
}
